IF OBJECT_ID('ssf.gbl_update_variable_description') IS NOT NULL
	DROP PROCEDURE ssf.gbl_update_variable_description
GO

CREATE PROCEDURE ssf.gbl_update_variable_description
	@variable_name NVARCHAR(128)
	, @variable_description NVARCHAR(2048) = NULL
	, @modified_date DATETIME2(7) = NULL OUTPUT
AS
/*
<object_header>

<object_summary>
	<purpose><![CDATA[

	]]></purpose>
</object_summary>

</object_header>
*/

SET NOCOUNT ON

-- ============
/*<code_comment><![CDATA[declare dynamic sql variables]]></code_comment>*/
DECLARE	@proc_error INT
			, @sql_error INT
			, @error_message NVARCHAR(2048)
			, @error_line INT
			, @row_count BIGINT
			, @nsql NVARCHAR(MAX)
			, @crlf CHAR(2) = CHAR(13) + CHAR(10)
			, @quote CHAR(1) = CHAR(39)
			, @tab CHAR(1) = CHAR(9)
-- ============

-- ============
/*<code_comment><![CDATA[declare local variables]]></code_comment>*/
DECLARE	@variable_exists BIT
			, @current_modified_date DATETIME2(7)
-- ============

-- ============
/*<code_comment><![CDATA[initialize output parameters]]></code_comment>*/
SET @modified_date = NULL
-- ============

-- ============
/*<code_comment><![CDATA[check parameters]]></code_comment>*/
IF NULLIF(@variable_name, N'') IS NULL
BEGIN
	RAISERROR('@variable_name cannot be blank or null.', 16, 1)
	RETURN 1
END
-- ============

-- ============
/*<code_comment><![CDATA[get variable attributes]]></code_comment>*/
SET @variable_exists = ssf.gbl_exists_variable(@variable_name)

IF ISNULL(@variable_exists, 0) <> 1
BEGIN
	RAISERROR('The variable <%s> does not exist.', 16, 1, @variable_name)
	RETURN 1
END

SET @current_modified_date = ssf.gbl_get_modified_date(@variable_name)

IF @current_modified_date IS NULL
BEGIN
	RAISERROR('Could not get current modified_date of the variable <%s>.', 16, 1, @variable_name)
	RETURN 1
END
-- ============

-- ============
/*<code_comment><![CDATA[set variable description]]></code_comment>*/
UPDATE ssf.gbl_variable WITH (ROWLOCK)
SET variable_description = @variable_description
	, modified_date = SYSDATETIME()
WHERE variable_name = @variable_name
AND modified_date = @current_modified_date

SELECT @sql_error = @@ERROR
	, @row_count = @@ROWCOUNT

IF @sql_error <> 0
OR @row_count <> 1
BEGIN
	RAISERROR('The UPDATE of the table <ssf.gbl_variable> failed.', 16, 1)
	RETURN @sql_error
END
-- ============

-- ============
/*<code_comment><![CDATA[get modified_date]]></code_comment>*/
SET @modified_date = ssf.gbl_get_modified_date(@variable_name)
-- ============

RETURN @@ERROR
GO
